package jheej5024;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class J03_MemberDAO implements J03_IMemberDAO{

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:jheej5024/jheej5024@112.216.253.125:1521:xe";
	private String insertSQL = "insert into J03MEMBER values(exam.nextval,?,?,?,?,sysdate)";
	private String selectSQL = "select * from J03Member order by mno desc";
	private String updateSQL = "update J03MEMBER set passwd=?, name=?, email=?,regdate=sysdate where mno=?";
	private String deleteSQL = "delete J03MEMBER where mno=?";
	
	
	@Override
	public boolean insertMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			//Class.forName(DRV);
			//conn = DriverManager.getConnection(URL);
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			
			// ? 매개변수에 대입할 값 지정
			
			pstmt.setString(1, m.getUserid());
			pstmt.setString(2, m.getPasswd());
			pstmt.setString(3, m.getName());
			pstmt.setString(4, m.getEmail());
					
			pstmt.executeUpdate();
			result = true;
			
			
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn,pstmt,null);
		}
		
		
		return result;
	} // insertMember
	
	//JDBC Conenction 객체를 생성하는 메서드
	private Connection openDatabase(){
		try{
		Class.forName(DRV);
		return DriverManager.getConnection(URL);
		}catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}
	
	
	//JDBC 객체를 닫는 메서드
	private void closeDatabase(Connection c, PreparedStatement p, ResultSet r) {
		if(r != null)try {r.close();r = null;}catch(Exception e) {}
		if(p != null)try {p.close();p = null;}catch(Exception e) {}
		if(c != null)try {c.close();c = null;}catch(Exception e) {}
		
	}

	//select 문으로 전체 회원정보를 조회한 후
	//MemberVO를 내용으로 가지는
	//동적배열 List로 결과를넘김
	
	@Override
	public List <J03_MemberVO> selectmember() {
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
		List <J03_MemberVO> result = new ArrayList<>();
	    
	   
		
		try {
			conn = openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				J03_MemberVO m = new J03_MemberVO();
				m.setMno(rs.getString("mno"));
				m.setUserid(rs.getString("userid"));
				m.setPasswd(rs.getString("passwd"));
				m.setName(rs.getString("name"));
				m.setEmail(rs.getString("email"));
				m.setRegdate(rs.getString("regdate"));
				result.add(m);
			}
			
			
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			closeDatabase(conn, pstmt, rs);
		}
		
		 return result;
	}

	@Override
	public boolean updateMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = openDatabase();
			pstmt=conn.prepareStatement(updateSQL);
			pstmt.setString(1,m.getPasswd());
			pstmt.setString(2,m.getName());
			pstmt.setString(3,m.getEmail());
			pstmt.setString(4,m.getMno());
			pstmt.executeUpdate();
int cnt = pstmt.executeUpdate();
if(cnt==0)System.out.println("명령은 잘 실행됐지만, 수정된 데이터는 없음");	
			result = true;
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn,pstmt,null);
		}
		return result;
	}
	

	@Override
	public boolean deleteMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = openDatabase();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1,m.getMno());
			pstmt.executeUpdate();
			int cnt = pstmt.executeUpdate();
			if(cnt==0)System.out.println("명령은 잘 실행됐지만, 수정된 데이터는 없음");	
			result = true;
		} catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn,pstmt,null);
		}
		
		return result;
	}
	
	
	
	
}
